package com.easymall.dao.impl;

import com.easymall.dao.ProdDao;
import com.easymall.domain.Category;
import com.easymall.domain.Prod;
import com.easymall.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProdDaoImplMariaDB implements ProdDao {
    @Override
    public void init() {

    }

    @Override
    public boolean saveProd(Prod prod) throws SQLException {
        String sql = "INSERT INTO prod (name, imgurl, description, cid) VALUES (?,?,?,?)";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, prod.getName());
            preparedStatement.setString(2, prod.getImgUrl());
            preparedStatement.setString(3, prod.getDescription());
            preparedStatement.setInt(4, prod.getCategory().getId());
            int result = preparedStatement.executeUpdate();
            if (1 == result) {
                resultSet = preparedStatement.getGeneratedKeys();
                if (resultSet.next()) {
                    prod.setId(resultSet.getInt(1));
                    return true;
                }
            }
            return false;
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public boolean deleteProd(int pid) throws SQLException {
        String sql = "UPDATE prod SET status=? WHERE id=?";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, Prod.STATUS_DELETE);
            preparedStatement.setInt(2, pid);
            int result = preparedStatement.executeUpdate();
            if (1 == result) {
                return true;
            }
            return false;
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public boolean updateProd(Prod prod) throws SQLException {
        // TODO
        return false;
    }

    @Override
    public Prod getProdById(int pid) throws SQLException {
        String sql = "SELECT prod.id, prod.name, prod.imgurl, prod.description, prod.status, c.id, c.name, c.description" +
                " FROM prod" +
                " LEFT JOIN category c on prod.cid = c.id" +
                " WHERE prod.id = ? AND prod.status = ?;";
        Prod prod = null;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, pid);
            preparedStatement.setInt(2, Prod.STATUS_NORMAL);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                prod = new Prod();
                prod.setId(resultSet.getInt("prod.id"));
                prod.setName(resultSet.getString("prod.name"));
                prod.setImgUrl(resultSet.getString("prod.imgurl"));
                prod.setDescription(resultSet.getString("prod.description"));
                prod.setStatus(resultSet.getInt("prod.status"));
                Category category = new Category();
                category.setId(resultSet.getInt("c.id"));
                category.setName(resultSet.getString("c.name"));
                category.setDescription(resultSet.getString("c.description"));
                prod.setCategory(category);
            }
            return prod;
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public List<Prod> listProdAll() throws SQLException {
        String sql = "SELECT prod.id, prod.name, prod.imgurl, prod.description, prod.status, c.id, c.name, c.description" +
                " FROM prod" +
                " LEFT JOIN category c on prod.cid = c.id" +
                " WHERE prod.status = ?";
        List<Prod> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, Prod.STATUS_NORMAL);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Prod prod = new Prod();
                prod.setId(resultSet.getInt("prod.id"));
                prod.setName(resultSet.getString("prod.name"));
                prod.setImgUrl(resultSet.getString("prod.imgurl"));
                prod.setDescription(resultSet.getString("prod.description"));
                prod.setStatus(resultSet.getInt("prod.status"));
                Category category = new Category();
                category.setId(resultSet.getInt("c.id"));
                category.setName(resultSet.getString("c.name"));
                category.setDescription(resultSet.getString("c.description"));
                prod.setCategory(category);
                list.add(prod);
            }
            return list;
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }
}
